package com.pentofab.payrollmanagement.database.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
import com.pentofab.payrollmanagement.dao.model.Holiday;
import com.pentofab.payrollmanagement.dao.model.Leave;
import com.pentofab.payrollmanagement.database.LeaveRepository;
import com.pentofab.payrollmanagement.exception.BusinessException;
import com.pentofab.payrollmanagement.exception.DatabaseException;
import com.pentofab.payrollmanagement.utilities.PayRollUtility;

public class LeaveRepositoryImpl implements LeaveRepository {

	
	Connection conn = null;

	public LeaveRepositoryImpl() {

		conn = CreateDBConnection.cteateConnectionObj();
	}
	
	public float getLeaveBalance(String empId, String leaveType) throws Exception {
		

		PreparedStatement preparedStatement = conn
				.prepareStatement(LeaveTableSQLConstants.leaveBalance);
		preparedStatement.setString(1, empId);
		preparedStatement.setString(2, leaveType);
		preparedStatement.setString(3, leaveType);
		System.out.println("preparedStatement : "+preparedStatement);
		try {
			ResultSet rs = preparedStatement.executeQuery();
			if (!rs.isBeforeFirst()) {
				return 9999999;
			}

			if (rs.next()) {
				
				return rs.getFloat(1);
			} 
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
		return 0;
	}

	public void applyforLeave(Leave leaveObject) throws Exception {
		
		/*(EID, TYPE, From_Date, To_Date, No_of_Days, 
	Approver, Reason)*/
		PreparedStatement preparedStatement = conn
				.prepareStatement(LeaveTableSQLConstants.applyLeave);
		
		preparedStatement.setString(1, leaveObject.getEmployee().getEmpId());
		preparedStatement.setString(2, leaveObject.getLeaveType());
		preparedStatement.setString(3, leaveObject.getFromDate());
		preparedStatement.setString(4, leaveObject.getToDate());
		preparedStatement.setDouble(5, leaveObject.getNoOfDays());
		preparedStatement.setString(6, leaveObject.getApprover());
		preparedStatement.setString(7, leaveObject.getReson());
		preparedStatement.setString(8, leaveObject.getHalfDay());
		
		PayRollUtility.debug(preparedStatement.toString());
		try {
			int status = preparedStatement.executeUpdate();
			PayRollUtility.debug("applyforLeave status : " + status);
		}  catch (Exception e) {
			e.printStackTrace();
			throw new DatabaseException(0);
		}
		
	}

	@Override
	public ArrayList<Leave> getLeaveHistory(String empId)
			throws Exception {
		
		PreparedStatement preparedStatement = conn
				.prepareStatement(LeaveTableSQLConstants.leaveHistory);
		preparedStatement.setString(1,empId);
		preparedStatement.setString(2, "01-01-"+PayRollUtility.getYear());
		PayRollUtility.debug(preparedStatement.toString());
		try {
			ResultSet rs = preparedStatement.executeQuery();
			if (!rs.isBeforeFirst()) {
				return null;
			}
			 ArrayList<Leave> leaveList = new  ArrayList<Leave>();
			while (rs.next()) {
				Leave leave = new Leave();
				leave.setLeaveType(rs.getString(LeaveTableSQLConstants.TYPE));
				leave.setNoOfDays(Double.parseDouble(rs.getString(LeaveTableSQLConstants.No_of_Days)));
				leave.setFromDate(rs.getString(LeaveTableSQLConstants.From_Date));
				leave.setToDate(rs.getString(LeaveTableSQLConstants.To_Date));
				leave.setReson(rs.getString(LeaveTableSQLConstants.Reason));
				
				leaveList.add(leave);
			} 
			System.out.println("leaveList : "+leaveList.size());
			return leaveList;
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new DatabaseException(0);
		}
		
	}

	@Override
	public void deleteLeave(String empId, String from_DATE, String to_DATE)
			throws Exception {

		PreparedStatement preparedStatement = conn
				.prepareStatement(LeaveTableSQLConstants.cancelLeave);
		preparedStatement.setString(1, empId);
		preparedStatement.setString(2, from_DATE);
		preparedStatement.setString(3, to_DATE);
		try{
			System.out.println("preparedStatement : "+preparedStatement);
			preparedStatement.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
			throw new DatabaseException(0);
		}
	}

	@Override
	public boolean empOnLeave(String empId, String date) throws Exception {

		PreparedStatement preparedStatement = conn
				.prepareStatement(LeaveTableSQLConstants.empOnLeave);
		preparedStatement.setString(1, empId);
		preparedStatement.setString(2, date);
		preparedStatement.setString(3, date);
		preparedStatement.setString(4, date);
		preparedStatement.setString(5, date);
		preparedStatement.setString(6, date);
		preparedStatement.setString(7, date);
		
		try{
			System.out.println("preparedStatement : "+preparedStatement);
			ResultSet rs = preparedStatement.executeQuery();
			if(!rs.isBeforeFirst()){
				return false;
			}
		}catch(Exception e){
			e.printStackTrace();
			throw new DatabaseException(0);
		}
		return true;
	}

	@Override
	public ArrayList<Holiday> getHolidays() throws Exception {
		
		PreparedStatement preparedStatement = conn
				.prepareStatement(LeaveTableSQLConstants.getHolidays);
		try{
			ArrayList<Holiday> holidaysList = new ArrayList<Holiday>();
			System.out.println("preparedStatement : "+preparedStatement);
			ResultSet rs = preparedStatement.executeQuery();
			
			while(rs.next()){
				
				Holiday holiday = new Holiday();
				holiday.setDate(rs.getString(LeaveTableSQLConstants.hDate));
				holiday.setReason(rs.getString(LeaveTableSQLConstants.hdescription));
				holiday.setNationalHoliday(rs.getBoolean(LeaveTableSQLConstants.hDate));
				holidaysList.add(holiday);
				
			}
			return holidaysList;
		}catch(Exception exception){
			
			throw new BusinessException("Eror while getting holidays detail.");
		}
	}

	@Override
	public void addholiday(String date, String description, boolean isNationalHoliday) throws Exception {

		PreparedStatement preparedStatement = conn
				.prepareStatement(LeaveTableSQLConstants.addHoliday);
		preparedStatement.setString(1, date);
		preparedStatement.setString(2, description);
		preparedStatement.setBoolean(3, isNationalHoliday);
		System.out.println("preparedStatement : "+preparedStatement);
		try {

			preparedStatement.executeUpdate();
		} catch (MySQLIntegrityConstraintViolationException exception) {
			throw new BusinessException("Entry already present for the date : "
					+ date);
		} catch (Exception exception) {

			throw new BusinessException("Error while adding holiday for date ; " + date);
		}
	}

	@Override
	public void deleteHoliday(String date) throws Exception {
		
		PreparedStatement preparedStatement = conn
				.prepareStatement(LeaveTableSQLConstants.deleteHoliday);
		preparedStatement.setString(1, date);
		
		try{
			
			preparedStatement.executeUpdate();
		}catch(Exception exception){
			
			throw new BusinessException("Error while deleting holiday for date : "+date);
		}
	}
	
	@Override
	public HashMap<String, String> getLeaveFromMaster() throws BusinessException{

		try {

			return null;
		} catch (Exception e) {
			throw new BusinessException(e, e.getMessage());
		}

	}
	
	public void updateLeaveMaster(double el,double cl,double sl) throws BusinessException{

		try {

		} catch (Exception e) {
			throw new BusinessException(e, e.getMessage());
		}

	}
	
}
